-- 触发器
-- 1.触发器是与表有关的数据库对象，在满足定义条件时触发，并执行触发器中定义的语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性。
-- 2.举个例子，比如你现在有两个表【用户表】和【日志表】，当一个用户被创建的时候，就需要在日志表中插入创建的log日志，如果在不使用触发器的情况下，
-- 你需要编写程序语言逻辑才能实现，但是如果你定义了一个触发器，触发器的作用就是当你在用户表中插入一条数据的之后帮你在日志表中插入一条日志信息。当然触发器并不是只能进行插入操作，还能执行修改，删除。
-- 创建触发器：
create trigger trigger_name trigger_time trigger_event on table_name for each row trigger_stmt;
-- trigger_name：触发器的名称
-- tirgger_time：触发时机，为BEFORE或者AFTER，指定了触发执行的时间，在事件之前或是之后
-- trigger_event：触发事件，为INSERT、DELETE或者UPDATE
-- tb_name：表示建立触发器的表明，就是在哪张表上建立触发器
-- trigger_stmt：触发器的程序体，可以是一条SQL语句或者是用BEGIN和END包含的多条语句
-- FOR EACH ROW表示任何一条记录上的操作满足触发事件都会触发该触发器
-- 所以可以说MySQL创建以下六种触发器：
-- BEFORE INSERT,BEFORE DELETE,BEFORE UPDATE,AFTER INSERT,AFTER DELETE,AFTER UPDATE
-- 触发器是一种特殊的存储过程，它在插入，删除或修改特定表中的数据时触发执行，它比数据库本身标准的功能有更精细和更复杂的数据控制能力。
-- 创建有多个执行语句的触发器：
create trigger 触发器名 before|after 触发事件
on 表名 for each row
begin
    执行语句列表(表示需要执行的多个语句，不同语句用分号隔开)
end;
-- 用户users表
CREATE TABLE users (
  id int(11) unsigned NOT NULL AUTO_INCREMENT,
  name varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL,
  add_time int(11) DEFAULT NULL,
  PRIMARY KEY (id),
  KEY name (name(250)) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=1000001 DEFAULT CHARSET=latin1;
-- 日志logs表
CREATE TABLE logs (
  Id int(11) NOT NULL AUTO_INCREMENT,
  log varchar(255) DEFAULT NULL COMMENT '日志说明',
  PRIMARY KEY (Id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='日志表';
-- 需求是：当在users中插入一条数据，就会在logs中生成一条日志信息。
create trigger user_log after insert on users for each row 
begin
    declare s1 varchar(40) character set utf8;
    declare s2 varchar(40) character set utf8;-- 设置字符集
    set s2 = 'is created';
    set s1 = concat(NEW.name,s2);
    insert into logs(log) values(s1);
    insert into users(name,add_time) values ('Daniel',now());
end;
-- 查看触发器
show triggers; -- 无法查询指定的触发器
-- 删除触发器
drop trigger user_log;
-- 在triggers表中查看触发器信息，所有触发器信息都存储在information_schema数据库下的triggers表中
select * from information_schema.triggers;
select * from information_schema.triggers where trigger_name = 'user_log';

-- 事务控制(TCL)
-- 1、事务就是一组原子性的 sql，或者说一个独立的工作单元。事务就是说，要么 MySQL 引擎会全部执行这一组sql语句，要么全部都不执行。
-- 2、一般来说，事务是必须满足4个条件（ACID）：：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）
    -- 原子性：事务的整个操作是一个整体，不可以分割，要么全部成功，要么全部失败。
    -- 一致性：事务操作的前后，数据表中的数据没有变化。
    -- 隔离性：事务操作是相互隔离不受影响的。
    -- 持久性：数据一旦提交，不可改变，永久的改变数据表数据。
-- 3、事务管理操作：
    -- 开启事务管理：开启之后，下面的sql语句并不会马上执行并把结果写到表中，而是会写到事务日志中。
 -- start transaction;
    -- 回退操作：回退会清掉开始事务管理之后写到事务日志中的内容，即恢复到开启事务管理之前。
 -- 语法：rollback;
 -- 注意：回退操作只是回退"写"的内容，对于普通的读表select语句不能回退。
    -- 事务提交：将sql语句的结果写到数据表中。
 -- 语法：commit:
-- 4、事务控制语句：
    -- BEGIN 或 START TRANSACTION 显式地开启一个事务；
    -- COMMIT 也可以使用 COMMIT WORK，不过二者是等价的。COMMIT 会提交事务，并使已对数据库进行的所有修改成为永久性的；
    -- ROLLBACK 也可以使用 ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；
    -- SAVEPOINT identifier，SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；
    -- RELEASE SAVEPOINT identifier 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；
    -- ROLLBACK TO identifier 把事务回滚到标记点；
    -- SET TRANSACTION 用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。

-- 5、MYSQL 事务处理主要有两种方法：
    -- 1、用 BEGIN, ROLLBACK, COMMIT来实现
 -- BEGIN 开始一个事务
 -- ROLLBACK 事务回滚
 -- COMMIT 事务确认
    -- 2、直接用 SET 来改变 MySQL 的自动提交模式:
 -- SET AUTOCOMMIT=0 禁止自动提交
 -- SET AUTOCOMMIT=1 开启自动提交

-- 6、实际演练：银行转账的例子：
create table bank_account(id int not null auto_increment primary key, name varchar (30) comment '姓名', balance decimal (18,2) comment '账户余额');
insert into bank_account(id, name, balance) values ( 1,'张三',0);
insert into bank_account(id, name, balance) values ( 2,'李四',0);
update bank_account set balance = balance+ 1000 where id = 1;
-- 查看自动提交是否开启,默认为 1 ,开启状态。
select @@autocommit;
-- 开启一个事务
start transaction;
-- 张三给李四转账 100 
update bank_account set balance = balance - 100 where id = 1;
update bank_account set balance = balance+ 100 where id = 2;
-- 提交事务
commit;
-- 查看表的状态,发现已经更改完毕
select * from bank_account; 

-- 又开启了一个事务
start transaction;
-- 想进行再转账 100 的业务
update bank_account set balance = balance - 100 where id = 1;
update bank_account set balance = balance+ 100 where id = 2;
-- 发现出现错误了,想取消这个事务,那么就使用rollback 回滚到开启事务之前
rollback;
-- 再次查看,发现未更改
select * from bank_account;

-- 开启一个事务
begin;
insert into bank_account(id, name, balance) values ( 3,'Daniel',1200);
commit;
select * from bank_account;

-- 开启了一个事务
start transaction;
-- 创建回滚点
savepoint before_bank;
-- 想进行再转账 100 的业务
update bank_account set balance = balance - 100 where id = 3;
update bank_account set balance = balance+ 100 where id = 2;
-- 发现出现错误了,想取消这个事务,那么就使用rollback to 回滚到转账业务前
rollback to before_bank;
update bank_account set balance = balance - 200 where id = 3;
update bank_account set balance = balance+ 200 where id = 1;
commit;
-- 再次查看,发现未更改
select * from bank_account;
-- 注意：回滚点在事务管理关闭(rollback或commit之后)之后失效，不要在事务之外使用回滚点。

-- 7、默认的事务管理：
    -- 默认情况下，mysql的事务管理是关闭（自动事务）的，语句的结果会马上写到数据表中。
 -- 可以通过show variable like 'autocommit';来查看是否开启自动事务，值为1为自动事务已开启，为0则为关闭。
    -- 关闭自动事务：set autocommit =0;【关闭后需要commit来执行每一条语句，相当于开始了事务管理】
 -- 不过注意的是set autocommit针对的是会话变量，所以这个设置只在此次会话连接中生效。
    -- 查看自动事务提交 autocommit
    SHOW VARIABLES LIKE 'autocommit';

-- 8、事务的隔离级别：
    -- read uncommitted：出现脏读、幻读、不可重复读
    -- read committed：避免脏读，出现脏读和不可重读读
    -- repeatale read：避免脏读、不可重复读，出现幻读
    -- serializable：避免脏读、幻读、不可重复读，但效率底下
    -- mysql中默认 第三个隔离级别 repeatable read
    -- oracle中默认 第二个隔离级别 read committed
    -- 查看隔离级别：select @@tx_isolation;
    -- 设置隔离级别：set session(当前)|global(全局) transaction isolation level 隔离级别;

-- 数据库范式
-- 第一范式(1NF)：要求有主键，并且要求每一个字段原子性不可再分
-- 第二范式(2NF)：满足第一范式，要求所有非主键字段完全依赖主键，不能产生部分依赖
-- 第三范式(3NF)：满足第二范式，所有非主键字段和主键字段之间不能产生传递依赖，消除传递依赖，保证每个属性都直接依赖于主键
-- 逆范式：逆范式是指打破范式，通过增加冗余或重复的数据来提高数据库的性能。

-- 数据库实体间有三种对应关系：一对一、一对多、多对多
-- 一对一关系示例：
    -- 一个学生对应一个学生档案材料 每个人都有唯一的身份证号
-- 一对多关系示例：
    -- 一个学生只属于一个班，但这个班有多名学生
-- 多对多关系示例：
    -- 一个学生可以选择多门课，一门课也可以有多名学生
    -- 一个人可以有多个角色，一个角色可以有多个人

-- MySQL数据库引擎
-- 1.InnoDB存储引擎：
    -- InnoDB是事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁定和外键，InnoDB是默认的MySQL引擎。
    -- InnoDB主要特性有：
 -- 1、InnoDB给MySQL提供了具有提交、回滚和崩溃恢复能力的事物安全（ACID兼容）存储引擎。InnoDB锁定在行级并且也在SELECT语句中提供一个类似Oracle的非锁定读。这些功能增加了多用户部署和性能。在SQL查询中，可以自由地将InnoDB类型的表和其他MySQL的表类型混合起来，甚至在同一个查询中也可以混合
 -- 2、InnoDB是为处理巨大数据量的最大性能设计。它的CPU效率可能是任何其他基于磁盘的关系型数据库引擎锁不能匹敌的
 -- 3、InnoDB存储引擎完全与MySQL服务器整合，InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池。InnoDB将它的表和索引在一个逻辑表空间中，表空间可以包含数个文件（或原始磁盘文件）。这与MyISAM表不同，比如在MyISAM表中每个表被存放在分离的文件中。InnoDB表可以是任何尺寸，即使在文件尺寸被限制为2GB的操作系统上
 -- 4、InnoDB支持外键完整性约束，存储表中的数据时，每张表的存储都按主键顺序存放，如果没有显示在表定义时指定主键，InnoDB会为每一行生成一个6字节的ROWID，并以此作为主键
 -- 5、InnoDB被用在众多需要高性能的大型数据库站点上
 -- 6、InnoDB不创建目录，使用InnoDB时，MySQL将在MySQL数据目录下创建一个名为ibdata1的10MB大小的自动扩展数据文件，以及两个名为ib_logfile0和ib_logfile1的5MB大小的日志文件

-- 2.MyISAM存储引擎
    -- MyISAM基于ISAM存储引擎，并对其进行扩展。它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM拥有较高的插入、查询速度，但不支持事物。MyISAM主要特性有：
 -- 1、大文件（达到63位文件长度）在支持大文件的文件系统和操作系统上被支持
 -- 2、当把删除和更新及插入操作混合使用的时候，动态尺寸的行产生更少碎片。这要通过合并相邻被删除的块，以及若下一个块被删除，就扩展到下一块自动完成
 -- 3、每个MyISAM表最大索引数是64，这可以通过重新编译来改变。每个索引最大的列数是16
 -- 4、最大的键长度是1000字节，这也可以通过编译来改变，对于键长度超过250字节的情况，一个超过1024字节的键将被用上
 -- 5、BLOB和TEXT列可以被索引
 -- 6、NULL被允许在索引的列中，这个值占每个键的0~1个字节
 -- 7、所有数字键值以高字节优先被存储以允许一个更高的索引压缩
 -- 8、每个MyISAM类型的表都有一个AUTO_INCREMENT的内部列，当INSERT和UPDATE操作的时候该列被更新，同时AUTO_INCREMENT列将被刷新。所以说，MyISAM类型表的AUTO_INCREMENT列更新比InnoDB类型的AUTO_INCREMENT更快
 -- 9、可以把数据文件和索引文件放在不同目录
 -- 10、每个字符列可以有不同的字符集
 -- 11、有VARCHAR的表可以固定或动态记录长度
 -- 12、VARCHAR和CHAR列可以多达64KB
 -- 13、使用MyISAM引擎创建数据库，将产生3个文件。文件的名字以表名字开始，扩展名之处文件类型：frm文件存储表定义、数据文件的扩展名为.MYD（MYData）、索引文件的扩展名时.MYI（MYIndex）

-- 3.MEMORY存储引擎
    -- MEMORY存储引擎将表中的数据存储到内存中，未查询和引用其他表数据提供快速访问。MEMORY主要特性有：
 -- 1、MEMORY表的每个表可以有多达32个索引，每个索引16列，以及500字节的最大键长度
 -- 2、MEMORY存储引擎执行HASH和BTREE缩影
 -- 3、可以在一个MEMORY表中有非唯一键值
 -- 4、MEMORY表使用一个固定的记录长度格式
 -- 5、MEMORY不支持BLOB或TEXT列
 -- 6、MEMORY支持AUTO_INCREMENT列和对可包含NULL值的列的索引
 -- 7、MEMORY表在所由客户端之间共享（就像其他任何非TEMPORARY表）
 -- 8、MEMORY表内存被存储在内存中，内存是MEMORY表和服务器在查询处理时的空闲中，创建的内部表共享
 -- 9、当不再需要MEMORY表的内容时，要释放被MEMORY表使用的内存，应该执行DELETE FROM或TRUNCATE TABLE，或者删除整个表（使用DROP TABLE）
-- 查看引擎
show engines;
show table status from test;
-- 4.存储引擎的选择
-- 如果要提供提交、回滚、崩溃恢复能力的事物安全（ACID兼容）能力，并要求实现并发控制，InnoDB是一个好的选择
-- 如果数据表主要用来插入和查询记录，则MyISAM引擎能提供较高的处理效率
-- 如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存中的Memory引擎，MySQL中使用该引擎作为临时表，存放查询的中间结果
-- 如果只有INSERT和SELECT操作，可以选择Archive，Archive支持高并发的插入操作，但是本身不是事务安全的。Archive非常适合存储归档数据，如记录日志信息可以使用Archive

-- explain
-- expain出来的信息有10列，分别是id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra
--     id:选择标识符
--     select_type:表示查询的类型。
--     table:输出结果集的表
--     partitions:匹配的分区
--     type:表示表的连接类型，常用的类型有： ALL、index、range、 ref、eq_ref、const、system、NULL（从左到右，性能从差到好）
--     possible_keys:表示查询时，可能使用的索引
--     key:表示实际使用的索引
--     key_len:索引字段的长度
--     ref:列与索引的比较
--     rows:扫描出的行数(估算的行数)
--     filtered:按表条件过滤的行百分比
--     Extra:执行情况的描述和说明
 -- Using where:不用读取表中所有信息，仅通过索引就可以获取所需数据，这发生在对表的全部的请求列都是同一个索引的部分的时候，表示mysql服务器将在存储引擎检索行后再进行过滤
 -- Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询，常见 group by ; order by
 -- Using filesort：当Query中包含 order by 操作，而且无法利用索引完成的排序操作称为“文件排序”

-- mysql优化
    -- explain：做 MySQL 优化，我们要善用 explain 查看 SQL 执行计划。
 -- 要重点关注的数据：
 -- • type列，连接类型。一个好的 sql 语句至少要达到range级别。杜绝出现all级别
 -- • key列，使用到的索引名。如果没有选择索引，值是NULL。可以采取强制索引方式
 -- • key_len列，索引长度
 -- • rows列，扫描行数。该值是个预估值
 -- • extra列，详细说明。注意常见的不太友好的值有：Using filesort, Using temporary
    explain select * from emp;
    -- SQL 语句中 IN 包含的值不应过多
    explain select * from emp where deptno in (10,20);
    -- SELECT 语句务必指明字段名称,SELECT * 增加很多不必要的消耗（cpu、io、内存、网络带宽）；增加了使用覆盖索引的可能性；当表结构发生改变时，前断也需要更新。
    select empno,ename,deptno from emp;
    -- 当只需要一条数据的时候，使用 limit 1
    select empno,ename,deptno from emp limit 1;
    -- 如果排序字段没有用到索引，就尽量少排序
    -- 如果限制条件中其他字段没有索引，尽量少用 or
    -- 尽量用 union all 代替 union .union 和 union all 的差异主要是前者需要将结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的 CPU 运算，加大资源消耗及延迟。当然，union all 的前提条件是两个结果集没有重复数据。
    -- 不使用ORDER BY RAND()
    select * from emp order by rand() limit 4;
    -- 区分in和exists， not in和not exists
 -- select * from 表A where id in (select id from 表B); 该 sql 语句相当于
 -- select * from 表A where exists(select * from 表B where 表B.id=表A.id);
 -- 区分 in 和 exists 主要是造成了驱动顺序的改变（这是性能变化的关键），如果是 exists，那么以外层表为驱动表，先被访问，如果是 IN，那么先执行子查询。所以 IN 适合于外表大而内表小的情况；EXISTS 适合于外表小而内表大的情况。
 -- 关于 not in 和 not exists，推荐使用 not exists，不仅仅是效率问题，not in 可能存在逻辑问题。
 -- 如何高效的写出一个替代 not exists 的 sql 语句？select colname … from A表 where a.id not in (select b.id from B表)
 -- 高效的 sql 语句：select colname … from A表 Left join B表 on where a.id = b.id where b.id is null
    -- 使用合理的分页方式以提高分页的效率
 -- select id,name from table_name limit 866613, 20
 -- 使用上述 sql 语句做分页的时候，可能有人会发现，随着表数据量的增加，直接使用 limit 分页查询会越来越慢。
 -- 优化的方法如下：可以取前一页的最大行数的 id，然后根据这个最大的id来限制下一页的起点。比如此列中，上一页最大的 id 是 866612。sql 可以采用如下的写法：
 -- select id,name from table_name where id> 866612 limit 20
    -- 分段查询
    -- 避免在 where 子句中对字段进行 null 值判断
    -- 不建议使用 % 前缀模糊查询，但可以使用 % 后缀
    -- 避免在where子句中对字段进行表达式操作
    -- 避免隐式类型转换
    -- 对于联合索引来说，要遵守最左前缀法则
    -- 必要时可以使用 force index 来强制查询走某个索引
    -- 注意范围查询语句

-- 用户和权限管理
-- 1、创建用户：
-- 方法一：create user '用户名'@'IP地址' identified by '密码';
create user 'Daniel'@'127.0.0.1' identified by '123456';
-- 方法二：insert into mysql.user(user,host,password,ssl_cipher,x509_issuer,x509_subject) values('用户名','IP地址',password('密码'),'','','');flush privileges;-- password 在MySQL5.7中 改为 authentication_string
insert into user(user,host,authentication_string,ssl_cipher,x509_issuer,x509_subject) values('Alisa','127.0.0.1',password('123456'),'','','');flush privileges;
-- 方法三：grant select on *.* to '用户名'@'IP地址' identified by '密码'; flush privileges;
grant select on *.* to 'Xiao'@'127.0.0.1' identified by '123456'; flush privileges;

-- 2、删除用户：
-- drop user '用户名'@'IP地址';
drop user 'Daniel'@'127.0.0.1';
-- delete from mysql.user where user = '用户名' and host = 'IP地址';flush privileges;
delete from mysql.user where user = 'Alisa' and host = '127.0.0.1';flush privileges;

-- 3、修改用户密码：
-- 修改root用户密码：
-- set password = password('newpassword');flush privileges;
-- update mysql.user set authentication_string = password('newpassword') where user = 'root' and host = 'localhost';flush privileges;
-- root修改其他用户密码：
-- set password for 'Alisa'@'127.0.0.1' =password('newpassword');flush privileges;
-- update mysql.user set password=password('newpassword') where  user = '用户名' and host = 'IP地址';flush privileges;
-- grant select on *.* to '用户名'@'IP地址' identified by 'newpassword';FLUSH PRIVILEGES;

-- 4、修改用户名：
-- rename user '用户名'@'IP地址' to '新用户名'@'IP地址';

-- flush privileges 命令本质上的作用是将当前user和privilige表中的用户信息/权限设置从mysql库(MySQL数据库的内置库)中提取到内存里。
-- MySQL用户数据和权限有修改后，搜索希望在"不重启MySQL服务"的情况下直接生效，那么就需要执行这个命令

-- 5、查看用户表：
select * from mysql.user;

-- MySQL权限管理
-- 1、授权：
grant 权限列表  on 库名.表名 to 用户名@'客户端主机'  [identified by '密码'  with参数];
-- 给该用户授予对数据库db1下的表tb1所有的操作权限
grant all privileges on db1.tb1 TO '用户名'@'IP'  
 
-- 给该用户授予对数据库db1下所有表进行查操作的权限
grant select on db1.* TO '用户名'@'IP'   
  
-- 给该用户授予对所有数据库下所有表进行查和增的操作     
grant select,insert on *.* TO '用户名'@'IP'   
    
-- 用户只能在该IP段下才能(通配符%表示任意)对所有数据库下所有表进行查操作
grant select on *.* TO '用户名'@'192.168.1.%'     
  
-- 用户能用户可以在任意IP下(通配符%表示任意)对所有数据库下所有表进行查操作    
grant select on *.* TO '用户名'@'%'   

-- 限制用户每小时的查询数量
grant select on *.* to '用户名'@'IP地址' identified by '123456' with max_queries_per_hour 5;
    -- MAX_QUERIES_PER_HOUR：                 -- 定义每小时允许执行的查询数
    -- MAX_UPDATES_PER_HOUR：                 -- 定义每小时允许执行的更新数
    -- MAX_CONNECTIONS_PER_HOUR：             -- 定义每小时可以建立的连接数
    -- MAX_USER_CONNECTIONS：                 -- 定义单个用户同时可以建立的连接数

-- 2、查看权限：
show grants for 'Daniel'@'127.0.0.1';

-- 3、回收权限：
revoke 权限列表 on 库名.表名 from "用户名"@'IP';
-- REVOKE DELETE ON *.*  FROM '用户名'@'%';      -- 回收部分权限
-- REVOKE ALL PRIVILEGES FROM '用户名'@'%';      -- 回收所有权限
